using System;
using System.IO;
using MySql.Data.MySqlClient;
using System.Data;
using System.Runtime.InteropServices;
namespace Test {
	class Konto {
		static void MainX() {
			DAL loca=new DAL();
			//loca.NonQry("drop table konto create table konto(id int identity,day date,ref varchar(100),code varchar(3),bal decimal(10,2),rem decimal(10,2))");
			//loca.NonQry("truncate table konto");
			string[] lines=File.ReadAllLines("acc20130508.log");
			DataTable lastEntry=loca.Query("select top 1 ref,convert(char(10),day,20)day,rem from konto order by id desc");
			int cnt1=0;

			int cnt2=0;
			foreach(string line in lines)
			{
				string[] arr=line.Split('\t');
				if(lastEntry.Rows[0]["ref"].ToString().Trim()==arr[1].Trim()&&lastEntry.Rows[0]["day"].ToString()==DateTime.ParseExact(arr[0],"dd MMM yy",null).ToString("yyyy-MM-dd")&&float.Parse(lastEntry.Rows[0]["rem"].ToString())==float.Parse(arr[5]))
					break;
				cnt1++;
			}
			for(int i=cnt1-1;i>=0;i--)
			{
				string[] arr=lines[i].Split('\t');
				DateTime dat=DateTime.ParseExact(arr[0],"dd MMM yy",null);
				string refer=arr[1].Trim().Replace("'","''");
				string code=arr[2];
				if(refer=="INTEREST (NET)")
					code="NET";
				if(refer.Contains("ADJ"))
					code="ADJ";
				float balanceIn=0;
				float balanceOut=0;
				float.TryParse(arr[3],out balanceIn);
				float.TryParse(arr[4],out balanceOut);
				float bal=balanceIn-balanceOut;
				float rem=float.Parse(arr[5]);
				if((int)loca.Scalar("select count(*)from konto where day='{0}' and ref='{1}' and code='{2}' and bal={3} and rem={4}",dat.ToString("yyyy-MM-dd"),refer,code,bal,rem)==0)
				{
					loca.NonQry("insert into konto(day,ref,code,bal,rem)values('{0}','{1}','{2}',{3},{4})",dat.ToString("yyyy-MM-dd"),refer,code,bal,rem);
					cnt2++;
				}
			}
			Console.WriteLine(cnt2);
			MainC();

			KontoGraph.MainX();
		}
		static void MainZ() {
			DataTable tab=new DataTable();
			MySqlDataAdapter adp=new MySqlDataAdapter("show tables","server=localhost;database=test;uid=sysuser;pwd=syspass");
			adp.Fill(tab);
		}
		static void MainC() {
			MAL mal=new MAL();
			//mal.NonQry("create table konto(id int auto_increment primary key,day date,ref varchar(100),code varchar(3),bal decimal(10,2),rem decimal(10,2))");
			//mal.NonQry("truncate table konto");
			string[] lines=File.ReadAllLines("acc20130508.log");
			DataTable lastEntry=mal.Query("select ref,day,rem from konto order by id desc limit 1");
			if(lastEntry.Rows.Count==0)
			{
				Array.Reverse(lines);
				foreach(string line in lines)
				{
					string[] arr=line.Split('\t');
					DateTime dat=DateTime.ParseExact(arr[0],"dd MMM yy",null);
					string refer=arr[1].Replace("'","''").Trim();
					string code=arr[2];
					if(refer=="INTEREST (NET)")
						code="NET";
					if(refer.Contains("ADJ"))
						code="ADJ";
					float balanceIn=0;
					float balanceOut=0;
					float.TryParse(arr[3],out balanceIn);
					float.TryParse(arr[4],out balanceOut);
					float bal=balanceIn-balanceOut;
					float rem=float.Parse(arr[5]);
					if((long)mal.Scalar("select count(*)from konto where day='{0}' and ref='{1}' and code='{2}' and bal={3} and rem={4}",dat.ToString("yyyy-MM-dd"),refer,code,bal,rem)==0)
					{
						mal.NonQry("insert into konto(day,ref,code,bal,rem)values('{0}','{1}','{2}',{3},{4})",dat.ToString("yyyy-MM-dd"),refer,code,bal,rem);
					}
				}
			}
			int cnt1=0;
			int cnt2=0;
			foreach(string line in lines)
			{
				string[] arr=line.Split('\t');
				DateTime lastEnt=(DateTime)lastEntry.Rows[0]["day"];
				if(lastEntry.Rows[0]["ref"].ToString().Trim()==arr[1].Trim()&&lastEnt.ToString("yyyy-MM-dd")==DateTime.ParseExact(arr[0],"dd MMM yy",null).ToString("yyyy-MM-dd")&&float.Parse(lastEntry.Rows[0]["rem"].ToString())==float.Parse(arr[5]))
					break;
				cnt1++;
			}
			for(int i=cnt1-1;i>=0;i--)
			{
				string[] arr=lines[i].Split('\t');
				DateTime dat=DateTime.ParseExact(arr[0],"dd MMM yy",null);
				string refer=arr[1].Trim().Replace("'","''");
				string code=arr[2];
				if(refer=="INTEREST (NET)")
					code="NET";
				if(refer.Contains("ADJ"))
					code="ADJ";
				float balanceIn=0;
				float balanceOut=0;
				float.TryParse(arr[3],out balanceIn);
				float.TryParse(arr[4],out balanceOut);
				float bal=balanceIn-balanceOut;
				float rem=float.Parse(arr[5]);
				if((long)mal.Scalar("select count(*)from konto where day='{0}' and ref='{1}' and code='{2}' and bal={3} and rem={4}",dat.ToString("yyyy-MM-dd"),refer,code,bal,rem)==0)
				{
					mal.NonQry("insert into konto(day,ref,code,bal,rem)values('{0}','{1}','{2}',{3},{4})",dat.ToString("yyyy-MM-dd"),refer,code,bal,rem);
					cnt2++;
				}
			}
			Console.WriteLine(cnt2);
		}
	}
}
